# @Project: diablo
# @File: rewrite_no_hit_agreement_price
# @Time: 2023/9/26 11:39
# @User: Jolly
# @Email: jolly@007vin.com

import json
import copy
import pymysql
import time
from datetime import datetime
import threading
import traceback

from pymongo import MongoClient, InsertOne, UpdateOne
from pymongo.errors import ConnectionFailure
from dbutils.pooled_db import PooledDB

"""
复写库存定价，并下架对应库存
判定：
    当日的订单库存中，有定价，但是实际库存没有定价的，需要将价格回写到实际库存，并下架对应库存
"""


class RewriteNoHitAgreementPrice:

    def __init__(self):

        start_time = time.time()

        # self.merchant_id = merchant_id

        # 初始化数据库连接

        # 本地
        # self.diablo_conn_2_0 = self.get_mongo_connection("127.0.0.1", "27017")

        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 2.0测试库
        # self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.154", "27017")

        # 正式mysql
        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            # client = MongoClient(f"mongodb://{host}:{port}",
            #                      maxPoolSize=100,
            #                      minPoolSize=1,
            #                      maxIdleTimeMS=30000,
            #                      waitQueueTimeoutMS=2000)

            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=100,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        # 本地
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='127.0.0.1',
        #     user='root',
        #     port=3306,
        #     passwd='12345678',
        #     db='users',
        #     use_unicode=True)

        # 线上
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # 测试
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def get_today_inquiry_data(self, merchant_id):
        """
        获取今日的询价单
        """
        connection = self.diablo_conn_2_0

        # 将日期时间对象格式化为字符串
        formatted_datetime = datetime.now().strftime('%Y-%m-%d') + " 00:00:00"
        # formatted_datetime = "2023-08-09 00:00:00"
        print(formatted_datetime)
        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["ddl_inquiry"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "create_time": {
                        "$gt": formatted_datetime
                    }
                }
                docs = conn.find(filter_dict)

                if conn.count_documents(filter_dict) == 0:
                    return [], True

                data = [inquiry for inquiry in docs]

                return data, False

        except Exception as e:
            print("获取今日询价单数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def get_merchant_data(self):
        """
        获取所有商家信息，然后遍历写入看板数据
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 查询商家id，加入列表返回
            merchant_ids = []
            merchant_sql = f"select DISTINCT(merchant_id) merchant_id from diablo_merchant "
            cursor.execute(merchant_sql)
            merchant_data = cursor.fetchall()

            for merchant in merchant_data:
                merchant_ids.append(merchant[0])

            return merchant_ids

        except Exception as e:
            print(f"获取商家信息失败 {e}")
        finally:
            mysql_conn.close()

    def get_bind_merchant(self, merchant_id):
        """
        获取绑定库存商家
        """

        stack_data = {}

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            bind_sql = f"""select bind_merchant_stock, stock_key, stock_erp from diablo_merchant where is_delete_time=0
             and merchant_id = '{merchant_id}'"""

            cursor.execute(bind_sql)

            result = cursor.fetchall()

            if len(result) > 0:

                bind_merchant_stock = result[0][0]

                if bind_merchant_stock != "":

                    sql = f"""select stock_key, stock_erp from diablo_merchant where is_delete_time=0 and
                     merchant_id = '{bind_merchant_stock}'"""

                    cursor.execute(sql)

                    result = cursor.fetchall()

                    if len(result) > 0:
                        stock_key = result[0][0]
                        stock_erp = result[0][1]

                        stack_data = {"stock_key": stock_key, "stock_erp": stock_erp}

        except Exception as e:
            print("获取绑定库存商家信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stack_data

    # 判断定价协议表是否已有对应数据
    def check_agreement_exist(self, identify, agreement_excel_id):

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_excel"]

                filter_dict = {
                    "identify": identify,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:
                    check = True

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
            return False, True
        finally:
            # 将连接放回连接池
            connection.close()

        return check, False

    def rewrite_stock_price(self, inquiry_data, merchant_id):
        """
        对满足条件的库存进行回写价格
        """

        # 获取当前客户对接库存信息
        bind_stock_data = self.get_bind_merchant(merchant_id)

        if not bind_stock_data:
            return

        # 遍历询价单，获取满足条件的库存（有订单定价，但是库存没有定价）
        for inquiry in inquiry_data:

            rewrite_stock_price_list = []

            # 只统计已报价的
            if inquiry.get("inquiry_status") < 5:
                continue

            # 获取零件
            part_list = inquiry.get("part_list")

            if not part_list:
                continue

            # 获取价格协议id
            inquiry_user = inquiry.get("inquiry_user")
            agreement_info = inquiry_user.get("agreement_info")
            agreement_excel_id = agreement_info.get("agreement_excel_id")
            if not agreement_excel_id:
                continue

            for part in part_list:

                # 获取库存
                stock_list = part.get("quality_price")

                if not stock_list:
                    continue

                for stock in stock_list:

                    identify = stock.get("identify")
                    price = stock.get("quality_price")

                    if identify == "" or identify is None:
                        continue

                    if price == 0:
                        continue

                    # 判断该库存是否有真实定价
                    _check, err = self.check_agreement_exist(identify, agreement_excel_id)
                    if err:
                        continue

                    if not _check:
                        # 说明没有定价
                        rewrite_stock_price_list.append({
                            "agreement_excel_id": agreement_excel_id,
                            "identify": identify,
                            "agreement_price": price,
                            "price_type": "customize",
                            "price_value": price,
                            "price_desc": f"¥ = {str(price)}",
                            "update_time": datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                            "user_id": "0000000000000000002",  # 当前用户id
                            "price_user": "系统回写定价",  # 当前用户名
                            "price_time": datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                        })

            # 回写到库存定价中，并将该库存下架
            agreement_operate_list = []
            stock_operate_list = []

            if len(rewrite_stock_price_list) > 0:
                for data in rewrite_stock_price_list:
                    agreement_operate_list.append(InsertOne(data))

                    # 将对应库存下架
                    # stock_operate_list.append(
                    #     UpdateOne({"identify": data.get("identify"), "app_key": bind_stock_data.get("stock_key")},
                    #               {'$set': {"status": 0}}))

            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行定价数据回写操作
                    db = connection["inventory_data"]
                    agreement_conn = db["agreement_excel_hongde"]
                    if len(agreement_operate_list) > 0:
                        # print(f"待写入数据 - {agreement_operate_list}")
                        agreement_conn.bulk_write(agreement_operate_list)
                    else:
                        print("无数据需要回写")

                    # 执行库存下架更新操作
                    # stock_conn = db[f"{bind_stock_data.get('stock_erp')}_meta_stocks"]
                    # stock_conn.bulk_write(stock_operate_list)

            except Exception as e:
                print("回写库存定价失败 - mongo：", traceback.format_exc())
            finally:
                # 将连接放回连接池
                connection.close()


if __name__ == "__main__":
    # 获取当前写入时间
    runtime = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(runtime)
    service = RewriteNoHitAgreementPrice()

    # 获取商家id列表
    merchant_ids = service.get_merchant_data()
    if len(merchant_ids) == 0:
        print("未查询到商家id信息")

    # for merchant_id in merchant_ids:
    merchant_id = "3"
    inquiry_data, check = service.get_today_inquiry_data(merchant_id)
    if check:
        print(f"商家id{merchant_id}今日无询价单数据")
    else:

        # 对满足条件的库存进行回写价格
        service.rewrite_stock_price(inquiry_data, merchant_id)